﻿-- =============================================
-- Script Template
-- =============================================
--DUMP  TRANSACTION  host8446263  WITH  NO_LOG   

CREATE PROCEDURE P_TrendChart_3D_ZHFB --3D号码综合分布图

AS

set nocount on

Create table #LotteryNumberList3D(id int identity(1,1),Isuse varchar(20),LotteryNumber varchar(100),
		B_0 int,B_1 int,B_2 int,B_3 int,B_4 int,B_5 int,B_6 int,B_7 int,B_8 int,B_9 int,
		Z3_0 INT,H_Z INT,H_ZW_0 INT,H_ZW_1 INT,H_ZW_2 INT,H_ZW_3 INT,H_ZW_4 INT,H_ZW_5 INT,H_ZW_6 INT,H_ZW_7 INT,
		H_ZW_8 INT,H_ZW_9 INT,L_012 VARCHAR(20),D1_0 INT,D1_1 INT, D1_2 INT,D1_3 INT,D1_4 INT,D1_5 INT,D1_6 INT,D1_7 INT,
		D1_8 INT,D1_9 INT,D2_0 INT,D2_1 INT,D2_2 INT,D2_3 INT,D2_4 INT,D2_5 INT,D2_6 INT,D2_7 INT,D2_8 INT,D2_9 INT,
		D3_0 INT,D3_1 INT,D3_2 INT,D3_3 INT,D3_4 INT,D3_5 INT,D3_6 INT,D3_7 INT,D3_8 INT,D3_9 INT)

declare @cur cursor 
declare @Isuse varchar(20), @WinLotterNumber varchar(100), @id int 

declare @B_1 int,@B_2 int,@B_3 int,@B_4 int,@B_5 int,@B_6 int,@B_7 int,@B_8 int,@B_9 int,@B_0 int,
		@Z3_0 INT,@H_Z INT,@H_ZW_0 INT,@H_ZW_1 INT,@H_ZW_2 INT,@H_ZW_3 INT,@H_ZW_4 INT,@H_ZW_5 INT,@H_ZW_6 INT,@H_ZW_7 INT,
		@H_ZW_8 INT,@H_ZW_9 INT,@L_012 VARCHAR(20),@D1_0 INT,@D1_1 INT, @D1_2 INT,@D1_3 INT,@D1_4 INT,@D1_5 INT,@D1_6 INT,@D1_7 INT,
		@D1_8 INT,@D1_9 INT,@D2_0 INT,@D2_1 INT,@D2_2 INT,@D2_3 INT,@D2_4 INT,@D2_5 INT,@D2_6 INT,@D2_7 INT,@D2_8 INT,@D2_9 INT,
		@D3_0 INT,@D3_1 INT,@D3_2 INT,@D3_3 INT,@D3_4 INT,@D3_5 INT,@D3_6 INT,@D3_7 INT,@D3_8 INT,@D3_9 INT

DECLARE @B INT,@S INT,@G INT



Create table #tb(id int,PlayNo varchar(20),WinLotteryNumber varchar(100))
		insert into #tb select Id,PlayNo, WinLotteryNumber from pm_plays  with(nolock) where WinLotteryNumber <> '' and LotteryID = 6 order by [EndTime] asc

set @Z3_0=0 SET @H_Z=0 SET @H_ZW_0=0 SET @H_ZW_1=0 SET @H_ZW_2=0 SET @H_ZW_3=0 SET @H_ZW_4=0 SET @H_ZW_5=0 SET @H_ZW_6=0
SET @H_ZW_7=0 SET @H_ZW_8=0 SET @H_ZW_9=0 SET @D1_0=0 SET @D1_1=0 SET @D1_2=0 SET @D1_3=0 SET @D1_4=0 SET @D1_5=0 
SET @D1_6=0 SET @D1_7=0 SET @D1_8=0 SET @D1_9=0SET @D2_0=0 SET @D2_1=0 SET @D2_2=0 SET @D2_3=0 SET @D2_4=0 SET @D2_5=0 
SET @D2_6=0 SET @D2_7=0 SET @D2_8=0 SET @D2_9=0SET @D3_0=0 SET @D3_1=0 SET @D3_2=0 SET @D3_3=0 SET @D3_4=0 SET @D3_5=0 
SET @D3_6=0 SET @D3_7=0 SET @D3_8=0 SET @D3_9=0

set @cur = cursor FAST_FORWARD for select PlayNo,WinLotteryNumber from #tb 

open @cur

fetch next from @cur into @Isuse,@WinLotterNumber

while @@fetch_status=0
begin

set @B = Substring(@WinLotterNumber,1,1) 
set @S = Substring(@WinLotterNumber,2,1)
set @G = Substring(@WinLotterNumber,3,1)

SET @B_0 = -1 SET @B_1 = -1 SET @B_2 = -1 SET @B_3 = -1 SET @B_4 = -1 SET @B_5 = -1 SET @B_6 = -1
SET @B_7 = -1 SET @B_8 = -1 SET @B_9 = -1 SET @Z3_0 =@Z3_0 + 1
SET @H_ZW_0=@H_ZW_0+1 SET @H_ZW_1=@H_ZW_1+1 SET @H_ZW_2=@H_ZW_2+1 SET @H_ZW_3=@H_ZW_3+1 SET @H_ZW_4=@H_ZW_4+1 
SET @H_ZW_5=@H_ZW_5+1 SET @H_ZW_6=@H_ZW_6+1 SET @H_ZW_7=@H_ZW_7+1 SET @H_ZW_8=@H_ZW_8+1 SET @H_ZW_9=@H_ZW_9+1
SET @D1_0=@D1_0+1 SET @D1_1=@D1_1+1 SET @D1_2=@D1_2+1 SET @D1_3=@D1_3+1 SET @D1_4=@D1_4+1 SET @D1_5=@D1_5+1 
SET @D1_6=@D1_6+1 SET @D1_7=@D1_7+1 SET @D1_8=@D1_8+1 SET @D1_9=@D1_9+1 
SET @D2_0=@D2_0+1 SET @D2_1=@D2_1+1 SET @D2_2=@D2_2+1 SET @D2_3=@D2_3+1 SET @D2_4=@D2_4+1 SET @D2_5=@D2_5+1 
SET @D2_6=@D2_6+1 SET @D2_7=@D2_7+1 SET @D2_8=@D2_8+1 SET @D2_9=@D2_9+1 
SET @D3_0=@D3_0+1 SET @D3_1=@D3_1+1 SET @D3_2=@D3_2+1 SET @D3_3=@D3_3+1 SET @D3_4=@D3_4+1 SET @D3_5=@D3_5+1 
SET @D3_6=@D3_6+1 SET @D3_7=@D3_7+1 SET @D3_8=@D3_8+1 SET @D3_9=@D3_9+1

IF(@B=0 OR @S=0 OR @G=0)
BEGIN
	SET @B_0 = 0

END

IF(@B=1 OR @S=1 OR @G=1)
BEGIN
	SET @B_1 = 1
END

IF(@S=2 OR @G=2 OR @B=2)
BEGIN
	SET @B_2 = 2
END

IF(@S=3 OR @G=3 OR @B=3)
BEGIN
	SET @B_3 = 3
END

IF(@S=4 OR @G=4 OR @B=4)
BEGIN
	SET @B_4 = 4		
END

IF(@S=5 OR @G=5 OR @B=5)
BEGIN
	SET @B_5 = 5		
END

IF(@S=6 OR @G=6 OR @B=6)
BEGIN
	SET @B_6 = 6		
END

IF(@S=7 OR @B=7 OR @G=7)
BEGIN
	SET @B_7 = 7
END

IF(@S=8 OR @B=8 OR @G=8)
BEGIN
	SET @B_8 = 8	
END

IF(@S=9 OR @B=9 OR @G=9)
BEGIN
	SET @B_9 = 9
END

-----组3间隔的统计
IF((@B = @S AND @S<>@G) OR (@B = @G AND @S<>@G) OR (@B <>@S  AND @G = @S))
BEGIN
	SET @Z3_0 = 0
END

--和值
set @H_Z = @B + @S +@G

--和值尾数统计
declare @H_Z_W varchar(10)
SET @H_Z_W =RTRIM(LTRIM(STR(@H_Z)))
declare @num int 
set @num = -1

if(LEN(@H_Z)=1)
BEGIN
	SET @num = substring(@H_Z_W,1,1)
END
else
begin
	SET @num = substring(@H_Z_W,2,1)
end

if(@num = 0)
begin
	set @H_ZW_0 = 0
end

if(@num = 1)
begin
	set @H_ZW_1 = 0
end

if(@num = 2)
begin
	set @H_ZW_2 = 0
end

if(@num = 3)
begin
	set @H_ZW_3 = 0
end

if(@num = 4)
begin
	set @H_ZW_4 = 0
end

if(@num = 5)
begin
	set @H_ZW_5 = 0
end

if(@num = 6)
begin
	set @H_ZW_6 = 0
end

if(@num = 7)
begin
	set @H_ZW_7 = 0
end

if(@num = 8)
begin
	set @H_ZW_8 = 0
end

if(@num = 9)
begin
	set @H_ZW_9 = 0
end

---012路
declare @L_0 INT
declare @L_1 INT
declare @L_2 INT
SET @L_0 =@B % 3
SET @L_1 =@S % 3 
SET @L_2 =@G % 3

SET @L_012 =RTRIM(LTRIM(STR(@L_0)))+RTRIM(LTRIM(STR(@L_1)))+RTRIM(LTRIM(STR(@L_2)))

---第1，2 ，3位的统计
if(@B = 0)
BEGIN
	SET @D1_0 = 0
END

if(@B = 1)
BEGIN
	SET @D1_1 = 0
END

if(@B = 2)
BEGIN
	SET @D1_2 = 0
END

if(@B = 3)
BEGIN
	SET @D1_3 = 0
END

if(@B = 4)
BEGIN
	SET @D1_4 = 0
END

if(@B = 5)
BEGIN
	SET @D1_5 = 0
END

if(@B = 6)
BEGIN
	SET @D1_6 = 0
END

if(@B = 7)
BEGIN
	SET @D1_7 = 0
END

if(@B = 8)
BEGIN
	SET @D1_8 = 0
END

if(@B = 9)
BEGIN
	SET @D1_9 = 0
END

if(@S = 0)
BEGIN
	SET @D2_0 = 0
END

if(@S = 1)
BEGIN
	SET @D2_1 = 0
END

if(@S = 2)
BEGIN
	SET @D2_2 = 0
END

if(@S = 3)
BEGIN
	SET @D2_3 = 0
END

if(@S = 4)
BEGIN
	SET @D2_4 = 0
END

if(@S = 5)
BEGIN
	SET @D2_5 = 0
END

if(@S = 6)
BEGIN
	SET @D2_6 = 0
END

if(@S = 7)
BEGIN
	SET @D2_7 = 0
END

if(@S = 8)
BEGIN
	SET @D2_8 = 0
END

if(@S = 9)
BEGIN
	SET @D2_9 = 0
END

if(@G = 0)
BEGIN
	SET @D3_0 = 0
END

if(@G = 1)
BEGIN
	SET @D3_1 = 0
END

if(@G = 2)
BEGIN
	SET @D3_2 = 0
END

if(@G = 3)
BEGIN
	SET @D3_3 = 0
END

if(@G = 4)
BEGIN
	SET @D3_4 = 0
END

if(@G = 5)
BEGIN
	SET @D3_5 = 0
END

if(@G = 6)
BEGIN
	SET @D3_6 = 0
END

if(@G = 7)
BEGIN
	SET @D3_7 = 0
END

if(@G = 8)
BEGIN
	SET @D3_8 = 0
END

if(@G = 9)
BEGIN
	SET @D3_9 = 0
END

insert into #LotteryNumberList3D values (@Isuse, @WinLotterNumber ,@B_0,@B_1,@B_2 ,@B_3 ,@B_4 ,@B_5,@B_6 ,
		@B_7 ,@B_8,@B_9,@Z3_0,@H_Z,@H_ZW_0,@H_ZW_1,@H_ZW_2,@H_ZW_3,@H_ZW_4,@H_ZW_5,@H_ZW_6,@H_ZW_7,@H_ZW_8,@H_ZW_9,
		@L_012,@D1_0,@D1_1,@D1_2,@D1_3,@D1_4,@D1_5,@D1_6,@D1_7,@D1_8,@D1_9,
		@D2_0,@D2_1,@D2_2,@D2_3,@D2_4,@D2_5,@D2_6,@D2_7,@D2_8,@D2_9,
		@D3_0,@D3_1,@D3_2,@D3_3,@D3_4,@D3_5,@D3_6,@D3_7,@D3_8,@D3_9)

fetch next from @cur into @Isuse, @WinLotterNumber

end

close @cur

select * from #LotteryNumberList3D

GO

